package cs4280.dao;

import cs4280.bean.House;
import cs4280.bean.Movie;
import cs4280.bean.Screening;
import cs4280.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class MovieDAO {

    public static enum RESULT {

        OK, DUPLICATE, FAIL
    };

    public ArrayList<Movie> getMovies() {
        ArrayList<Movie> movies = new ArrayList<Movie>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie];");
            while (rs != null && rs.next() != false) {
                Movie movie = new Movie();
                movie.setId(rs.getInt(1));
                movie.setName(rs.getString(2));
                movie.setDuration(rs.getInt(3));
                movie.setGenere(rs.getString(4));
                movie.setOpendate(rs.getString(5));
                movie.setMovieLang(rs.getString(6));
                movie.setActor(rs.getString(7));
                movie.setStoryLine(rs.getString(8));
                movie.setImgurl(rs.getString(9));
                movies.add(movie);
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movies;
    }

    public ArrayList<Movie> getSearchMovies(String keyword) {
        ArrayList<Movie> movies = new ArrayList<Movie>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie] WHERE [name] like '%" + keyword + "%'");
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                Movie movie = new Movie();
                movie.setId(rs.getInt(1));
                movie.setName(rs.getString(2));
                movie.setDuration(rs.getInt(3));
                movie.setGenere(rs.getString(4));
                movie.setOpendate(rs.getString(5));
                movie.setMovieLang(rs.getString(6));
                movie.setActor(rs.getString(7));
                movie.setStoryLine(rs.getString(8));
                movie.setImgurl(rs.getString(9));
                movies.add(movie);
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movies;
    }

    public ArrayList<Movie> getSortMovies(String sort, String order, String number) {
        ArrayList<Movie> movies = new ArrayList<Movie>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            int num;
            if (number.equalsIgnoreCase("all")) {
                num = 0;
            } else {
                num = Integer.parseInt(number);
            }
            ResultSet rs;
            if (order.equalsIgnoreCase("ascending")) {
                rs = stmt.executeQuery("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie] ORDER BY " + sort + " ASC");
            } else {
                rs = stmt.executeQuery("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie] ORDER BY " + sort + " DESC");
            }
            int i = 0;
            while (rs != null && rs.next() != false) {
                Movie movie = new Movie();
                movie.setId(rs.getInt(1));
                movie.setName(rs.getString(2));
                movie.setDuration(rs.getInt(3));
                movie.setGenere(rs.getString(4));
                movie.setOpendate(rs.getString(5));
                movie.setMovieLang(rs.getString(6));
                movie.setActor(rs.getString(7));
                movie.setStoryLine(rs.getString(8));
                movie.setImgurl(rs.getString(9));
                movies.add(movie);
                i++;
                if (i == num) {
                    break;
                }
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movies;
    }

    public ArrayList<Movie> getMovieStatistics() {
        ArrayList<Movie> movies = new ArrayList<Movie>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select distinct movie.id from [movie],[booking] where booking.movieid = movie.id;");


            while (rs != null && rs.next() != false) {
                Movie movie = new Movie();
                //get the movie id with booking record
                int movieid = rs.getInt(1);
                movie=this.getMovie(movieid);
               
                PreparedStatement pstmt = conn.prepareStatement(" select  count(*) as [count] from [movie],[booking] where movie.id = booking.movieid and movie.id="+movieid+";");
               // pstmt.setInt(1, movieid);

                ResultSet countSale = pstmt.executeQuery();
                while (countSale != null && countSale.next() != false) {
                    int sale = countSale.getInt(1);
                    movie.setSales(sale);
                    PreparedStatement pstmt_getname = conn.prepareStatement("select distinct name from [movie] where id=?");
                    pstmt_getname.setInt(1, movieid);
                    ResultSet movieName = pstmt_getname.executeQuery();

                    while (movieName != null && movieName.next() != false) {
                    movie.setName(movieName.getString(1));
                    }
                    
                    if (movieName != null) {
                        movieName.close();
                    }
                    if (pstmt_getname != null) {
                        pstmt_getname.close();
                    }



                    movies.add(movie);

                    
                }

                if (pstmt != null) {
                    pstmt.close();
                }
                
                if (countSale != null) {
                    countSale.close();
                }



            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movies;
    }

    public Movie getMovie(int id) {
        Movie movie = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie] WHERE [id] = ?;");
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                movie = new Movie();
                movie.setId(rs.getInt(1));
                movie.setName(rs.getString(2));
                movie.setDuration(rs.getInt(3));
                movie.setGenere(rs.getString(4));
                movie.setOpendate(rs.getString(5));
                movie.setMovieLang(rs.getString(6));
                movie.setActor(rs.getString(7));
                movie.setStoryLine(rs.getString(8));
                movie.setImgurl(rs.getString(9));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movie;
    }

    public Movie getMovie(String name) {
        Movie movie = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [name], [duration], [genere], [opendate], [movielang],[actor],[storyline],[imgurl] FROM [movie] WHERE [name] = ?;");
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                movie = new Movie();
                movie.setId(rs.getInt(1));
                movie.setName(rs.getString(2));
                movie.setDuration(rs.getInt(3));
                movie.setGenere(rs.getString(4));
                movie.setOpendate(rs.getString(5));
                movie.setMovieLang(rs.getString(6));
                movie.setActor(rs.getString(7));
                movie.setStoryLine(rs.getString(8));
                movie.setImgurl(rs.getString(9));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return movie;
    }

    public RESULT addMovie(Movie newMovie, Screening newScreen) {

        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [movie] WHERE [name] =?");
            pstmt.setString(1, newMovie.getName());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            //there is no duplicate movie

            if (rs != null && rs.next() != false) {
                result = MovieDAO.RESULT.DUPLICATE;
            } // return duplicate msg
            else {


                pstmt = conn.prepareStatement("INSERT INTO [movie]([name],[duration],[genere],[opendate],[movielang],[actor],[storyline],[imgurl]) VALUES(?,?,?,?,?,?,?,?)");
                pstmt.setString(1, newMovie.getName());
                pstmt.setInt(2, newMovie.getDuration());
                pstmt.setString(3, newMovie.getGenere());
                pstmt.setString(4, newMovie.getOpendate());
                pstmt.setString(5, newMovie.getMovieLang());
                pstmt.setString(6, newMovie.getActor());
                pstmt.setString(7, newMovie.getStoryLine());
                pstmt.setString(8, newMovie.getImgurl());
                int rows = pstmt.executeUpdate();

                MovieDAO dao = new MovieDAO();
                newMovie = dao.getMovie(newMovie.getName());


                House house = new House();
                HouseDAO houseDAO = new HouseDAO();
                house = houseDAO.getHouse(newScreen.getHouseid());

                int seatNum = house.getRownumber() * house.getColnumber();
                String[] seat = new String[seatNum];
                for (int i = 0; i < seatNum; i++) {
                    seat[i] = "0";
                }
                String seating = "";
                for (int j = 0; j < seatNum; j++) {
                    seating += seat[j];

                }
                //successfully add the movie
                if (rows > 0) {
                    PreparedStatement pstmt_screening = conn.prepareStatement("INSERT INTO [screening]([movieid],[showtime],[houseid],[price],[seating]) VALUES(?,?,?,?,?)");
                    pstmt_screening.setInt(1, newMovie.getId());
                    pstmt_screening.setString(2, newScreen.getShowtime());
                    pstmt_screening.setInt(3, newScreen.getHouseid());
                    pstmt_screening.setInt(4, newScreen.getPrice());
                    pstmt_screening.setString(5, seating);

                    int screen_update = pstmt_screening.executeUpdate();
                    if (screen_update > 0) {
                        result = MovieDAO.RESULT.OK;
                    }
                } //insert movie fail
                else {
                    result = MovieDAO.RESULT.FAIL;
                }

            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }

    public RESULT deleteMovie(Movie newMovie) {



        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [movie] WHERE [name] =?");
            pstmt.setString(1, newMovie.getName());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            BookingDAO bookingdao = new BookingDAO();

            //movie exists
            boolean isbooked = bookingdao.isBooked(newMovie);

            if (rs != null && rs.next() != false && isbooked == false) {
                pstmt = conn.prepareStatement("DELETE FROM [movie] WHERE [name]=?");
                pstmt.setString(1, newMovie.getName());

                int rows = pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM [screening] WHERE [movieid]=?");
                pstmt.setInt(1, newMovie.getId());

                int s = pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM [booking] WHERE [movieid]=?");
                pstmt.setInt(1, newMovie.getId());

                int b = pstmt.executeUpdate();


                //successfully delete the movie
                if (rows > 0) {

                    result = MovieDAO.RESULT.OK;
                } //insert movie fail


            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }

    public RESULT updateMovie(Movie movie) {

        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("UPDATE [movie] SET [duration]=?,[genere]=?,[opendate]=?,[movielang]=?,[actor]=?,[storyline]=? ,[imgurl]=? WHERE [name]=?");

            pstmt.setInt(1, movie.getDuration());
            pstmt.setString(2, movie.getGenere());

            pstmt.setString(3, movie.getOpendate());

            pstmt.setString(4, movie.getMovieLang());
            pstmt.setString(5, movie.getActor());
            pstmt.setString(6, movie.getStoryLine());
            pstmt.setString(7,movie.getImgurl());
            pstmt.setString(8, movie.getName());
            if (pstmt.executeUpdate() > 0) {

                result = MovieDAO.RESULT.OK;
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;
    }

    public String getMoviename(int movieid) {
        String moviename = null;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [name] FROM [movie] WHERE [id]=?");
            pstmt.setInt(1, movieid);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                moviename = rs.getString(1);
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }
        return moviename;
    }
}
